﻿using System;
using System.Collections.Generic;
using System.Text;
using DH.Share.Comm.InterFace;
using DH.Share.Comm.customerclass;
using System.Data.SqlClient;
using System.Data;
using System.Windows;
using System.Threading.Tasks;


namespace DH.Share.Comm.DataHelp
{
    public class SqlHelps : IDataHelps
    {
        private static string connectstring = $"server ='kk'; database ='master'; uid ='sa'; pwd ='1234'; connect timeout = 10";
        public static string ErrorInfo { get; private set;}
        public static DataTable CurrentDataTable { get; private set; }
        public static Int32 RowsCount { get; private set; }
 

        #region 返回数据库链接状态。
        public bool RetuLinkSqlBool()
        {
            if (!string.IsNullOrEmpty(connectstring))
            {
                try
                {
                    SqlConnection conn = new SqlConnection(connectstring);
                    conn.Open();
                    if (conn.State == ConnectionState.Open)
                    {
                        conn.Close();
                        return true;
                    }
                    else
                    {
                        conn.Close();
                        ErrorInfo= ErrorInfo ?? "数据源链接状态错误";
                        return false;
                    }
                }
                catch (Exception ex)
                {
                    ErrorInfo=ex.Message;
                    return false;
                }
            }
            else
            {
                ErrorInfo = ErrorInfo ?? "数据源链接字符串为空";
                return false;
            }
        }
        #endregion
  
        #region sql任务
        #region 通过查询语句返回查询结果集数据表GetSelect
        public Task<bool> GetSelect(string commandtxt)
        {
            DataSet ds = new DataSet();
            try
            {
                SqlConnection conn = new SqlConnection(connectstring);
                SqlCommand cmd = new SqlCommand(commandtxt, conn);
                SqlDataAdapter sdr = new SqlDataAdapter { SelectCommand = cmd };
                sdr.Fill(ds);
                cmd.Dispose();
                conn.Close();
                sdr.Dispose();
                if (ds.Tables.Count > 0)
                {
                    if (ds.Tables[0].Rows.Count > 0)
                    {
                        RowsCount = ds.Tables[0].Rows.Count;
                        CurrentDataTable = ds.Tables[0];
                        return Task.FromResult(true);
                    }
                    else
                    {
                        RowsCount = 0;
                        CurrentDataTable = null;
                        return Task.FromResult(false);
                    }
                }
                else
                {
                    ErrorInfo = ErrorInfo ?? "sql命令无法生成结果，请检查语句的正确性";
                    RowsCount = 0;
                    CurrentDataTable = null;
                    return Task.FromResult(false);
                }
            }
            catch (SqlException ex)
            {
                ErrorInfo = ErrorInfo ?? ex.Message;
                RowsCount = 0;
                CurrentDataTable = null;
                return Task.FromResult(false);
            }
            finally
            {
                ds.Dispose();
            }
        }
        #endregion

        #region 执行sql语句返回执行结果的布尔值只针对"insert，update,delete" GetIsChanged()
        private static async Task<bool> GetIsChanged(string commandtxt)
        {
            try
            {
                SqlConnection conn = new SqlConnection(connectstring);
                conn.Open();
                SqlCommand cmd = new SqlCommand(commandtxt, conn);
                int i = Convert.ToInt32(await cmd.ExecuteNonQueryAsync());
                conn.Close();
                cmd.Dispose();
                if (i > 0)
                {
                    return true;
                }
                else
                {
                    return false; ;
                }
            }
            catch (SqlException ex)
            {
                ErrorInfo=ErrorInfo ?? ex.Message;
                return false;
            }
        }
        #endregion

        #region 不带事务执行多条sql语句，返回bool结果ToRunNoTranChange
        private static async Task<bool> ToRunNoTranChange(List<String> sqlstringlist)
        {
            SqlConnection conn = new SqlConnection(connectstring);
            SqlCommand cmd = new SqlCommand() { Connection = conn };
            int i = 0;
            try
            {
                conn.Open();
                for (i = 0; i < sqlstringlist.Count; i++)
                {
                    cmd.CommandText = sqlstringlist[i];
                    await cmd.ExecuteNonQueryAsync();
                }
                return true;
            }
            catch (SqlException e)
            {
                ErrorInfo=ErrorInfo ?? e.Message + $"-- 第{i}条语句错误";
                return false ;
            }
            finally
            {
                conn.Close();
                cmd.Dispose(); 
            }
        }
        #endregion

        #region 执行多条sql语句，返回bool结果
        private static async Task<bool> ToRunAndTranChange(List<String> sqlstringlist)
        {
            SqlConnection conn = new SqlConnection(connectstring);
            int i = 0;
            SqlCommand cmd = new SqlCommand() { Connection = conn };
            SqlTransaction tr = conn.BeginTransaction();
            try
            {
                conn.Open();
                cmd.Transaction = tr;
                for (i = 0; i < sqlstringlist.Count; i++)
                {
                    string trsql = sqlstringlist[i];
                    if (trsql.Contains("@"))
                    { }
                    cmd.CommandText = trsql;
                    await cmd.ExecuteNonQueryAsync();
                }
                tr.Commit();
                return true;
            }
            catch (SqlException e)
            {
                ErrorInfo=ErrorInfo ??e.Message + $"-- 第{i}条语句错误";
                tr.Rollback();
                return false;
            }
            finally
            {
                conn.Close();
                cmd.Dispose();
                tr.Dispose();
            }
        }
        #endregion

        #region  将datatable数据批量插入表中
        private static async Task<bool> ToBulkData(DataTable dt, string tablename)
        {
            using (SqlConnection conn = new SqlConnection(connectstring))
            {
                conn.Open();
                SqlTransaction tran = conn.BeginTransaction();//开启事务
                SqlBulkCopy bulkCopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.CheckConstraints, tran)
                {
                    DestinationTableName = tablename.TbFullName()  //***代表要插入数据的表名
                };
                if (dt.Columns.Count > 0)
                {
                    foreach (DataColumn dc in dt.Columns)  //传入上述table
                    {
                        bulkCopy.ColumnMappings.Add(dc.ColumnName, dc.ColumnName);//将table中的列与数据库表这的列一一对应
                    }
                }
                try
                {
                    await bulkCopy.WriteToServerAsync(dt);
                    tran.Commit();
                    return true;
                }
                catch (SqlException ex)
                {
                    tran.Rollback();
                    ErrorInfo=ErrorInfo ?? ex.Message;
                    return false;
                }
                finally
                {
                    conn.Close();
                    bulkCopy.Close();
                }
            }
        }
        #endregion

        #region  将dr数据插入表中
        private static Task<Tuple<DataTable, int, bool>> InsertDrToSql(DataRow dr, string para = "")
        {
            var t = Tuple.Create<DataTable, int, bool>(null, 0, false);
            if (dr == null)
            {
                return Task.FromResult(t);
            }
            if (string.IsNullOrEmpty(para))
            {
                string insert = "";// ClsUserData.FromDrToString(dr, "insert")";
                try
                {
                    SqlConnection conn = new SqlConnection(connectstring);
                    conn.Open();
                    SqlCommand cmd = new SqlCommand(insert, conn);
                    int i = Convert.ToInt32(cmd.ExecuteNonQuery());
                    conn.Close();
                    cmd.Dispose();
                    if (i > 0)
                    {
                        t = Tuple.Create<DataTable, int, bool>(null, i, true);
                        return Task.FromResult(t);
                    }
                    else
                    {
                        t = Tuple.Create<DataTable, int, bool>(null, 0, true);
                        return Task.FromResult(t);
                    }
                }
                catch (SqlException ex)
                {
                    ErrorInfo = ErrorInfo ?? (ex.Message);
                    t = Tuple.Create<DataTable, int, bool>(null, -1, false);
                    return Task.FromResult(t);
                }
            }
            else
            {
                string insert = "";// ClsUserData.FromDrToString(dr, "insertpara");
                try
                {
                    SqlConnection conn = new SqlConnection(connectstring);
                    conn.Open();
                    SqlCommand cmd = new SqlCommand(insert, conn);
                    SqlDbType drtype = SqlDbType.VarChar;
                    foreach (DataColumn dc in dr.Table.Columns)
                    {
                        switch (dc.DataType.Name)
                        {
                            case "String":
                                {
                                    drtype = SqlDbType.NVarChar;
                                }
                                break;
                            case "DateTime":
                                {
                                    drtype = SqlDbType.DateTime;
                                }
                                break;
                            case "Boolean":
                                {
                                    drtype = SqlDbType.Bit;
                                }
                                break;
                            case "Int32":
                                {
                                    drtype = SqlDbType.Int;
                                }
                                break;
                            case "Byte[]":
                                {
                                    drtype = SqlDbType.Image;
                                }
                                break;
                        }
                        cmd.Parameters.AddWithValue($"@{dc.ColumnName}", drtype).Value = dr[dc.ColumnName];
                    }
                    int i = Convert.ToInt32(cmd.ExecuteNonQuery());
                    conn.Close();
                    cmd.Dispose();
                    if (i > 0)
                    {
                        t = Tuple.Create<DataTable, int, bool>(null, i, true);
                        return Task.FromResult(t);
                    }
                    else
                    {
                        t = Tuple.Create<DataTable, int, bool>(null, 0, true);
                        return Task.FromResult(t);
                    }
                }
                catch (SqlException ex)
                {
                    ErrorInfo = ErrorInfo ?? (ex.Message);
                    t = Tuple.Create<DataTable, int, bool>(null, -1, false);
                    return Task.FromResult(t);
                }
            }
        }
        #endregion

        #region  修改dr数据
        private static Task<Tuple<DataTable, int, bool>> UpdateDrToSql(DataRow dr, string para = "")
        {
            var t = Tuple.Create<DataTable, int, bool>(null, 0, false);
            if (dr == null)
            {
                return Task.FromResult(t);
            }
            if (string.IsNullOrEmpty(para))
            {
                string update = "";// ClsUserData.FromDrToString(dr, "update");
                try
                {
                    SqlConnection conn = new SqlConnection(connectstring);
                    conn.Open();
                    SqlCommand cmd = new SqlCommand(update, conn);
                    int i = Convert.ToInt32(cmd.ExecuteNonQuery());
                    conn.Close();
                    cmd.Dispose();
                    if (i > 0)
                    {
                        t = Tuple.Create<DataTable, int, bool>(null, i, true);
                        return Task.FromResult(t);
                    }
                    else
                    {
                        t = Tuple.Create<DataTable, int, bool>(null, 0, true);
                        return Task.FromResult(t);
                    }
                }
                catch (Exception ex)
                {
                    ErrorInfo =ErrorInfo ?? (ex.Message);
                    t = Tuple.Create<DataTable, int, bool>(null, -1, false);
                    return Task.FromResult(t);
                }
            }
            else
            {
                string update = "";// ClsUserData.FromDrToString(dr, "updatepara");
                try
                {
                    SqlConnection conn = new SqlConnection(connectstring);
                    conn.Open();
                    SqlCommand cmd = new SqlCommand(update, conn);
                    SqlDbType drtype = SqlDbType.VarChar;
                    foreach (DataColumn dc in dr.Table.Columns)
                    {
                        switch (dc.DataType.Name)
                        {
                            case "String":
                                {
                                    drtype = SqlDbType.NVarChar;
                                }
                                break;
                            case "DateTime":
                                {
                                    drtype = SqlDbType.DateTime;
                                }
                                break;
                            case "Boolean":
                                {
                                    drtype = SqlDbType.Bit;
                                }
                                break;
                            case "Int32":
                                {
                                    drtype = SqlDbType.Int;
                                }
                                break;
                            case "Byte[]":
                                {
                                    drtype = SqlDbType.Image;
                                }
                                break;
                        }
                        cmd.Parameters.AddWithValue($"@{dc.ColumnName}", drtype).Value = dr[dc.ColumnName];
                    }
                    int i = Convert.ToInt32(cmd.ExecuteNonQuery());
                    conn.Close();
                    cmd.Dispose();
                    if (i > 0)
                    {
                        t = Tuple.Create<DataTable, int, bool>(null, i, true);
                        return Task.FromResult(t);
                    }
                    else
                    {
                        t = Tuple.Create<DataTable, int, bool>(null, 0, true);
                        return Task.FromResult(t);
                    }
                }
                catch (Exception ex)
                {
                    ErrorInfo =ErrorInfo ?? (ex.Message);
                    t = Tuple.Create<DataTable, int, bool>(null, -1, false);
                    return Task.FromResult(t);
                }
            }
        }
        #endregion

        #region 创建并返回sql表的表结构
        private static Task<Tuple<DataTable, int, bool>> RetuTbCloneOrFull(string tablename, bool tbclone = true)
        {
            DataSet ds = new DataSet();
            string commandtxt = $"select * from {tablename.TbFullName()}";
            try
            {
                SqlConnection conn = new SqlConnection(connectstring);
                SqlCommand cmd = new SqlCommand(commandtxt, conn);
                SqlDataAdapter sdr = new SqlDataAdapter { SelectCommand = cmd };
                sdr.Fill(ds);
                cmd.Dispose();
                conn.Close();
                sdr.Dispose();
                int recnum = ds.Tables[0].Rows.Count;
                if (tbclone == true)
                {
                    var t = Tuple.Create<DataTable, int, bool>(ds.Tables[0].Clone(), recnum, true);
                    return Task.FromResult(t);
                }
                else
                {
                    var t = Tuple.Create<DataTable, int, bool>(ds.Tables[0], recnum, true);
                    return Task.FromResult(t);
                }
            }
            catch (SqlException ex)
            {
                ErrorInfo=ErrorInfo ?? (ex.Message);
                var t = Tuple.Create<DataTable, int, bool>(null, 0, false);
                return Task.FromResult(t);
            }
            finally
            {
                ds.Dispose();
            }
        }
        #endregion
        #endregion
    }
}    

